package com.infoguia.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.infoguia.to.CategoryTo;
import com.infoguia.to.CompanyTo;
import com.infoguia.to.PhoneTo;
import com.infoguia.to.StateTo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class InfoguiaDao {

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Database creation sql statement
	 */
	private SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, InfoguiaDB.DATABASE_NAME, null,
					InfoguiaDB.DATABASE_VERSION);
			Log.i("DatabaseHelper", "Base de datos creada satisfactoriamente: "
					+ InfoguiaDB.DATABASE_NAME + " - "
					+ InfoguiaDB.DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(InfoguiaDB.CREATE_CATEGORY);
				Log.i("onCreate", "SQL " + InfoguiaDB.CREATE_CATEGORY
						+ " ejecutado correctamente");
				db.execSQL(InfoguiaDB.CREATE_COMPANY);
				Log.i("onCreate", "SQL  " + InfoguiaDB.CREATE_COMPANY
						+ " ejecutado correctamente");
				db.execSQL(InfoguiaDB.CREATE_PHONE);
				Log.i("onCreate", "SQL  " + InfoguiaDB.CREATE_PHONE
						+ " ejecutado correctamente");
				db.execSQL(InfoguiaDB.CREATE_COMPANY_CATEGORY);
				Log.i("onCreate", "SQL  " + InfoguiaDB.CREATE_COMPANY_CATEGORY
						+ " ejecutado correctamente");
				db.execSQL(InfoguiaDB.CREATE_STATE);
				Log.i("onCreate", "SQL  " + InfoguiaDB.CREATE_STATE
						+ " ejecutado correctamente");
			} catch (Exception e) {
				Log.e("onCreate", "Error creado las tablas " + e.getCause());
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w("DatabaseHelper", "Upgrading database from version "
					+ oldVersion + " to " + newVersion
					+ ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + InfoguiaDB.T_CATEGORY + ";");
			db.execSQL("DROP TABLE IF EXISTS " + InfoguiaDB.T_COMPANY + ";");
			db.execSQL("DROP TABLE IF EXISTS " + InfoguiaDB.T_PHONE + ";");
			db.execSQL("DROP TABLE IF EXISTS " + InfoguiaDB.T_COMPANY_CATEGORY
					+ ";");
			db.execSQL("DROP TABLE IF EXISTS " + InfoguiaDB.T_STATE + ";");
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	public InfoguiaDao(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public InfoguiaDao open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public void update() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
	}

	/**
	 * Creando una nueva categoria
	 * 
	 * @param categoria
	 *            representa una instancia de la Clase Categoria
	 * @return rowId or -1 if failed
	 */
	public long createCategory(CategoryTo categoria) {
		ContentValues initialValues = new ContentValues();
		initialValues.put("name", categoria.getName());
		initialValues
				.put("is_principal", (categoria.isPrincipal() ? "Y" : "N"));
		initialValues.put("is_recomendada", (categoria.isRecomendada() ? "Y"
				: "N"));
		return mDb.insert(InfoguiaDB.T_CATEGORY, null, initialValues);
	}

	/**
	 * Creando una nueva entidad
	 * 
	 * @param categoria
	 *            representa una instancia de la Clase Categoria
	 * @return rowId or -1 if failed
	 */
	public long createState(StateTo state) {
		ContentValues initialValues = new ContentValues();
		initialValues.put("name", state.getName());
		return mDb.insert(InfoguiaDB.T_STATE, null, initialValues);
	}

	/**
	 * Creando una nueva Compania
	 * 
	 * @param company
	 *            representa una instancia de la Clase Company
	 * @return rowId or -1 if failed
	 */
	public long createCompany(CompanyTo company) {

		Long result = 0L;

		ContentValues initialValues = new ContentValues();
		initialValues.put("name", company.getName());
		initialValues.put("description", company.getDescription());
		initialValues.put("address", company.getAddress());
		initialValues.put("mail", company.getMail());
		initialValues.put("map", company.getMap());
		initialValues.put("logo", company.getLogo());
		initialValues.put("id_state", company.getEntidad().getId());
		result = mDb.insert(InfoguiaDB.T_COMPANY, null, initialValues);

		for (CategoryTo category : company.getCategory()) {
			initialValues = new ContentValues();
			initialValues.put("id_company", company.getId());
			initialValues.put("id_category", category.getId());
			result = mDb.insert(InfoguiaDB.T_COMPANY_CATEGORY, null,
					initialValues);
		}

		for (PhoneTo phone : company.getNumbers()) {
			this.createPhone(phone, company.getId());
		}

		return result;
	}

	/**
	 * Creando un nuevo telefono
	 * 
	 * @param phone
	 *            representa una instancia de la Clase PhoneTo
	 * @return rowId or -1 if failed
	 */
	public long createPhone(PhoneTo phone, Long idCompany) {
		ContentValues initialValues = new ContentValues();
		initialValues.put("code", phone.getCode());
		initialValues.put("number", phone.getNumber());
		initialValues.put("id_company", idCompany);
		return mDb.insert(InfoguiaDB.T_PHONE, null, initialValues);
	}

	/**
	 * Consultar una categoria por id
	 * 
	 * @return Cursor con el datelle de la categoria
	 */
	public CategoryTo getCategory(Long id) {

		Cursor mCursor;
		CategoryTo category = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, InfoguiaDB.KEY_ROWID + " = " + id,
					null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
				category = new CategoryTo();
				category.setId(mCursor.getLong(0));
				category.setName(mCursor.getString(1));
				category.setPrincipal(mCursor.getString(2)
						.equalsIgnoreCase("Y"));
				category.setRecomendada(mCursor.getString(3).equalsIgnoreCase(
						"Y"));
			}
		} catch (Exception ex) {
			Log.e("getCategory", "Error consultando la categoria " + id);
		}
		return category;
	}

	/**
	 * Consultar una categoria por id mode = full
	 * 
	 * @return Cursor con el datelle de la categoria mode = full
	 */
	public CategoryTo getCategoryFull(Long id) {

		Cursor mCursor;
		CategoryTo category = null;
		List<CompanyTo> companys;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, InfoguiaDB.KEY_ROWID + " = " + id,
					null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
				category = new CategoryTo();
				category.setId(mCursor.getLong(0));
				category.setName(mCursor.getString(1));
				category.setPrincipal(mCursor.getString(2)
						.equalsIgnoreCase("Y"));
				category.setRecomendada(mCursor.getString(3).equalsIgnoreCase(
						"Y"));
			}

			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
					new String[] { "id_company", "id_category" },
					"id_category = " + id, null, null, null, null, null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					companys.add(this.getCompany(mCursor.getLong(0)));
				}
				category.setCompany(companys);
			}

		} catch (Exception ex) {
			Log.e("getCategoryFull",
					"Error consultando la categoria mode = full");
		}
		return category;
	}

	/**
	 * Consultar una entidad por id
	 * 
	 * @return Objeto con el datelle del state
	 */
	public StateTo getState(Long id) {

		Cursor mCursor;
		StateTo state = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_STATE, new String[] {
					InfoguiaDB.KEY_ROWID, "name" }, InfoguiaDB.KEY_ROWID
					+ " = " + id, null, null, null, null, null);
			if (mCursor != null) {
				mCursor.moveToFirst();
				state = new StateTo();
				state.setId(mCursor.getLong(0));
				state.setName(mCursor.getString(1));
			}
		} catch (Exception ex) {
			Log.e("getState", "Error consultando el estado " + id);
		}
		return state;
	}

	/**
	 * Consultar compania por id
	 * 
	 * @return Objeto con el datelle de la compania
	 */
	public CompanyTo getCompany(Long id) {

		Cursor mCursor;
		CompanyTo company = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "logo", "id_state" },
					InfoguiaDB.KEY_ROWID + " = " + id, null, null, null, null,
					null);
			if (mCursor != null) {
				mCursor.moveToFirst();
				company = new CompanyTo();
				company.setId(mCursor.getLong(0));
				company.setName(mCursor.getString(1));
				company.setDescription(mCursor.getString(2));
				company.setAddress(mCursor.getString(3));
				company.setMail(mCursor.getString(4));
				company.setMap(mCursor.getString(5));
				company.setWeb(mCursor.getString(6));
				company.setEntidad(this.getState(mCursor.getLong(7)));
				company.setLogo(mCursor.getString(8));
				company.setNumbers(this.getPhones(company.getId()));
			}
		} catch (Exception ex) {
			Log.e("getCompany", "Error consultando la compania");
		}
		return company;
	}

	/**
	 * Consultar compania por id mode = full
	 * 
	 * @return Objeto con el datelle de la compania mode = full
	 */
	public CompanyTo getCompanyFull(Long id) {

		Cursor mCursor;
		CompanyTo company = null;
		List<CategoryTo> categorys;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state", "logo" },
					InfoguiaDB.KEY_ROWID + " = " + id, null, null, null, null,
					null);
			if (mCursor != null) {
				mCursor.moveToFirst();
				company = new CompanyTo();
				company.setId(mCursor.getLong(0));
				company.setName(mCursor.getString(1));
				company.setDescription(mCursor.getString(2));
				company.setAddress(mCursor.getString(3));
				company.setMail(mCursor.getString(4));
				company.setMap(mCursor.getString(5));
				company.setWeb(mCursor.getString(6));
				company.setEntidad(this.getState(mCursor.getLong(7)));
				company.setLogo(mCursor.getString(8));
				company.setNumbers(this.getPhones(company.getId()));
			}

			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
					new String[] { "id_company", "id_category" },
					"id_company = " + id, null, null, null, null, null);
			if (mCursor != null) {
				categorys = new ArrayList<CategoryTo>();
				while (mCursor.moveToNext()) {
					categorys.add(this.getCategory(mCursor.getLong(1)));
				}
				company.setCategory(categorys);
			}
		} catch (Exception ex) {
			Log.e("getCompanyFull", "Error consultando la compania");
		}
		return company;
	}

	public Long getNumCompanys() {
		Cursor c = mDb.rawQuery("select count(*) from " + InfoguiaDB.T_COMPANY,
				null);
		int g = 0;

		try {
			c.moveToFirst();
			g = c.getInt(0);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return (long) g;
	}

	/**
	 * Consultar telefono por id de compania
	 * 
	 * @return Lista con los telefonos de la compania
	 */
	public List<PhoneTo> getPhones(Long idCompany) {

		Cursor mCursor;
		List<PhoneTo> phones = null;
		PhoneTo phone = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_PHONE, new String[] {
					InfoguiaDB.KEY_ROWID, "code", "number", "id_company" },
					"id_company = " + idCompany, null, null, null, null, null);
			if (mCursor != null) {
				phones = new ArrayList<PhoneTo>();
				while (mCursor.moveToNext()) {
					phone = new PhoneTo();
					phone.setId(mCursor.getLong(0));
					phone.setCode(mCursor.getLong(1));
					phone.setNumber(mCursor.getLong(2));
					phones.add(phone);
				}
			}
		} catch (Exception ex) {
			Log.e("getPhones", "Error consultando los telefonos de la compania");
		}
		return phones;
	}

	/**
	 * Consultar una categoria por id
	 * 
	 * @return Lista con el datelle de los estados
	 */
	public List<StateTo> getStates() {

		Cursor mCursor;
		StateTo state = null;
		List<StateTo> states = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_STATE, new String[] {
					InfoguiaDB.KEY_ROWID, "name" }, null, null, null, null,
					null, null);
			if (mCursor != null) {
				states = new ArrayList<StateTo>();
				while (mCursor.moveToNext()) {
					state = new StateTo();
					state.setId(mCursor.getLong(0));
					state.setName(mCursor.getString(1));
					states.add(state);
				}
			}
		} catch (Exception ex) {
			Log.e("getStates", "Error consultando las entidades");
		}
		return states;
	}

	/**
	 * Consultar categorias recomendadas
	 * 
	 * @return Lista con el datelle de la categorias recomendadas
	 */
	public List<CategoryTo> getRecommendedCategories() {

		Cursor mCursor;
		CategoryTo category = null;
		List<CategoryTo> categorys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, "is_recomendada = 'Y'", null, null,
					null, null, null);
			if (mCursor != null) {
				categorys = new ArrayList<CategoryTo>();
				while (mCursor.moveToNext()) {
					category = new CategoryTo();
					category.setId(mCursor.getLong(0));
					category.setName(mCursor.getString(1));
					category.setPrincipal(mCursor.getString(2)
							.equalsIgnoreCase("Y"));
					category.setRecomendada(mCursor.getString(3)
							.equalsIgnoreCase("Y"));
					categorys.add(category);
				}
			}
		} catch (Exception ex) {
			Log.e("getCategoryByName",
					"Error consultando las categorias recomendadas");
		}
		return categorys;
	}

	/**
	 * Consultar categorias principales
	 * 
	 * @return Lista con el datelle de la categorias principales
	 */
	public List<CategoryTo> getMainCategories() {

		Cursor mCursor;
		CategoryTo category = null;
		List<CategoryTo> categorys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, "is_principal = 'Y'", null, null, null,
					null, null);
			if (mCursor != null) {
				categorys = new ArrayList<CategoryTo>();
				while (mCursor.moveToNext()) {
					category = new CategoryTo();
					category.setId(mCursor.getLong(0));
					category.setName(mCursor.getString(1));
					category.setPrincipal(mCursor.getString(2)
							.equalsIgnoreCase("Y"));
					category.setRecomendada(mCursor.getString(3)
							.equalsIgnoreCase("Y"));
					categorys.add(category);
				}
			}
		} catch (Exception ex) {
			Log.e("getCategoryByName",
					"Error consultando las categorias principales");
		}
		return categorys;
	}

	/**
	 * Consultar categorias por nombre
	 * 
	 * @return Lista con el datelle de la categorias
	 */
	public List<CategoryTo> getCategoryByName(String name) {

		Cursor mCursor;
		CategoryTo category = null;
		List<CategoryTo> categorys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, "name like '%" + name
					+ "%' and is_principal = 'N' and is_recomendada = 'N'",
					null, null, null, null, null);
			if (mCursor != null) {
				categorys = new ArrayList<CategoryTo>();
				while (mCursor.moveToNext()) {
					category = new CategoryTo();
					category.setId(mCursor.getLong(0));
					category.setName(mCursor.getString(1));
					category.setPrincipal(mCursor.getString(2)
							.equalsIgnoreCase("Y"));
					category.setRecomendada(mCursor.getString(3)
							.equalsIgnoreCase("Y"));
					category.setCompany(this.getCompanyByCategory(category
							.getId()));
					categorys.add(category);
				}
			}
		} catch (Exception ex) {
			Log.e("getCategoryByName",
					"Error consultando la categoria por nombre = " + name);
		}
		return categorys;
	}

	/**
	 * Consultar categorias por nombre y entidad
	 * 
	 * @return Lista con el datelle de la categorias
	 */
	public List<CategoryTo> getCategoryByState(String name, Long idState) {

		Cursor mCursor;
		CategoryTo category = null;
		List<CategoryTo> categorys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_CATEGORY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "is_principal",
					"is_recomendada" }, "name like '%" + name + "%'", null,
					null, null, null, null);
			if (mCursor != null) {
				categorys = new ArrayList<CategoryTo>();
				while (mCursor.moveToNext()) {
					category = new CategoryTo();
					category.setId(mCursor.getLong(0));
					category.setName(mCursor.getString(1));
					category.setPrincipal(mCursor.getString(2)
							.equalsIgnoreCase("Y"));
					category.setRecomendada(mCursor.getString(3)
							.equalsIgnoreCase("Y"));
					category.setCompany(this.getCompanyByCategory(idState,
							category.getId()));
					categorys.add(category);
				}
			}
		} catch (Exception ex) {
			Log.e("getCategoryByName",
					"Error consultando la categoria por nombre = " + name
							+ " y Entidad = " + idState);
		}
		return categorys;
	}

	/**
	 * Consultar compania por nombre
	 * 
	 * @return Lista con el datelle de la compania
	 */
	public List<CompanyTo> getCompanyByName(String name) {

		Cursor mCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state" }, "name like '%" + name
					+ "%'", null, null, null, null, null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					company.setName(mCursor.getString(1));
					company.setDescription(mCursor.getString(2));
					company.setAddress(mCursor.getString(3));
					company.setMail(mCursor.getString(4));
					company.setMap(mCursor.getString(5));
					company.setWeb(mCursor.getString(6));
					company.setEntidad(this.getState(mCursor.getLong(7)));
					company.setNumbers(this.getPhones(company.getId()));
					companys.add(company);
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByName",
					"Error consultando la compania por el nombre " + name);
		}
		return companys;
	}

	/**
	 * Consultar compania por nombre y entidad
	 * 
	 * @return Lista con el la compania
	 */
	public List<CompanyTo> getCompanyByState(String name, Long idState) {

		Cursor mCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state" }, "name like '%" + name
					+ "%' and id_state = " + idState, null, null, null, null,
					null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					company.setName(mCursor.getString(1));
					company.setDescription(mCursor.getString(2));
					company.setAddress(mCursor.getString(3));
					company.setMail(mCursor.getString(4));
					company.setMap(mCursor.getString(5));
					company.setWeb(mCursor.getString(6));
					company.setEntidad(this.getState(mCursor.getLong(7)));
					company.setNumbers(this.getPhones(company.getId()));
					companys.add(company);
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByState",
					"Error consultando la compania por el nombre " + name);
		}
		return companys;
	}

	/**
	 * Consultar companias por categoria
	 * 
	 * @return Lista con el la compania
	 */
	public List<CompanyTo> getCompanyByCategory(Long idCategory) {

		Cursor mCursor;
		Cursor tCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state", "logo" }, null, null,
					null, null, null, null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					tCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
							new String[] { "id_company", "id_category" },
							"id_company = " + company.getId(), null, null,
							null, null, null);
					if (tCursor != null) {
						while (tCursor.moveToNext()) {
							if (tCursor.getLong(1) == idCategory) {
								company.setName(mCursor.getString(1));
								company.setDescription(mCursor.getString(2));
								company.setAddress(mCursor.getString(3));
								company.setMail(mCursor.getString(4));
								company.setMap(mCursor.getString(5));
								company.setWeb(mCursor.getString(6));
								company.setEntidad(this.getState(mCursor
										.getLong(7)));
								company.setLogo(mCursor.getString(8));
								company.setNumbers(this.getPhones(company
										.getId()));
								companys.add(company);
							}
						}
					}
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByCategory",
					"Error consultando la compania por la categoria "
							+ idCategory);
		}
		return companys;
	}

	/**
	 * Consultar compania por nombre y categoria
	 * 
	 * @return Lista con el la compania
	 */
	public List<CompanyTo> getCompanyByCategory(String name, Long idCategory) {

		Cursor mCursor;
		Cursor tCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state" }, "name = '%" + name
					+ "%'", null, null, null, null, null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					tCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
							new String[] { "id_company", "id_category" },
							"id_company = " + company.getId(), null, null,
							null, null, null);
					if (tCursor != null) {
						if (tCursor.getLong(0) == idCategory) {
							company.setName(mCursor.getString(1));
							company.setDescription(mCursor.getString(2));
							company.setAddress(mCursor.getString(3));
							company.setMail(mCursor.getString(4));
							company.setMap(mCursor.getString(5));
							company.setWeb(mCursor.getString(6));
							company.setEntidad(this.getState(mCursor.getLong(7)));
							company.setNumbers(this.getPhones(company.getId()));
							companys.add(company);
						}
					}
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByCategory",
					"Error consultando la compania por el nombre " + name);
		}
		return companys;
	}

	/**
	 * Consultar compania por nombre y entidad y categoria
	 * 
	 * @return Lista con el la compania
	 */
	public List<CompanyTo> getCompanyByCategory(String name, Long idState,
			Long idCategory) {

		Cursor mCursor;
		Cursor tCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state" }, "name = '%" + name
					+ "%' and id_state = " + idState, null, null, null, null,
					null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					tCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
							new String[] { "id_company", "id_category" },
							"id_company = " + company.getId(), null, null,
							null, null, null);
					if (tCursor != null) {
						if (tCursor.getLong(0) == idCategory) {
							company.setName(mCursor.getString(1));
							company.setDescription(mCursor.getString(2));
							company.setAddress(mCursor.getString(3));
							company.setMail(mCursor.getString(4));
							company.setMap(mCursor.getString(5));
							company.setWeb(mCursor.getString(6));
							company.setEntidad(this.getState(mCursor.getLong(7)));
							company.setNumbers(this.getPhones(company.getId()));
							companys.add(company);
						}
					}
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByCategory",
					"Error consultando la compania por el nombre " + name);
		}
		return companys;
	}

	/**
	 * Consultar compania por nombre y entidad y categoria
	 * 
	 * @return Lista con el la compania
	 */
	public List<CompanyTo> getCompanyByCategory(Long idState, Long idCategory) {

		Cursor mCursor;
		Cursor tCursor;
		CompanyTo company = null;
		List<CompanyTo> companys = null;
		try {
			mCursor = mDb.query(true, InfoguiaDB.T_COMPANY, new String[] {
					InfoguiaDB.KEY_ROWID, "name", "description", "address",
					"mail", "map", "web", "id_state", "logo" }, "id_state = "
					+ idState, null, null, null, null, null);
			if (mCursor != null) {
				companys = new ArrayList<CompanyTo>();
				while (mCursor.moveToNext()) {
					company = new CompanyTo();
					company.setId(mCursor.getLong(0));
					tCursor = mDb.query(true, InfoguiaDB.T_COMPANY_CATEGORY,
							new String[] { "id_company", "id_category" },
							"id_company = " + company.getId(), null, null,
							null, null, null);
					if (tCursor != null) {
						while (tCursor.moveToNext()) {
							if (tCursor.getLong(1) == idCategory) {
								company.setName(mCursor.getString(1));
								company.setDescription(mCursor.getString(2));
								company.setAddress(mCursor.getString(3));
								company.setMail(mCursor.getString(4));
								company.setMap(mCursor.getString(5));
								company.setWeb(mCursor.getString(6));
								company.setEntidad(this.getState(mCursor
										.getLong(7)));
								company.setLogo(mCursor.getString(8));
								company.setNumbers(this.getPhones(company
										.getId()));
								companys.add(company);
							}
						}
					}
				}
			}
		} catch (Exception ex) {
			Log.e("getCompanyByCategory",
					"Error consultando la compania para las categorias "
							+ idCategory);
		}
		return companys;
	}

}
